﻿using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Runtime.InteropServices;
using System.Text;
using DataTable = System.Data.DataTable;
using Excel = Microsoft.Office.Interop.Excel;

namespace _058.Excel操作
{
    internal class Program
    {
        /**
		 * 读取excel一个工作表转换为csv格式
		 */
        static void GetCsv()
        {
            // 打开Excel文件
            Application excel = new Application();
            Workbook workbook = excel.Workbooks.Open(@"D:\1\DTC List_GEELY_G636.xlsx");
            // 得到第一个工作表
            Worksheet worksheet = (Worksheet)workbook.Sheets[1];
            // 获取工作表范围内的所有单元格数据
            Range range = worksheet.UsedRange;
            object[,] values = (object[,])range.Value2;
            // 将数据写入CSV文件中（以逗号分隔）
            using (StreamWriter writer = new StreamWriter(@"D:\1\csv_file.csv"))
            {
                for (int i = 1; i <= range.Rows.Count; i++)
                {
                    string rowString = "";
                    for (int j = 1; j <= range.Columns.Count; j++)
                    {
                        if (values[i, j] != null)
                            rowString += values[i, j].ToString() + ",";
                        else
                            rowString += ",";
                    }
                    writer.WriteLine(rowString.TrimEnd(','));
                }
                writer.Close();
            }
            // 关闭Excel应用程序，释放资源 

            workbook.Close(false);
            excel.Quit();
        }

        static void GetCsvToManySheet()
        {
            // 打开Excel文件
            Application excel = new Application();
            Workbook workbook = excel.Workbooks.Open(@"D:\1\DTC List_GEELY_G636.xlsx");
            // 创建新的CSV文件，并写入所有工资表数据 
            using (StreamWriter writer = new StreamWriter(@"D:\1\csv_file_many.csv"))
            {
                foreach (Worksheet worksheet in workbook.Worksheets)
                {
                    // 得到表名
                    Console.WriteLine(worksheet.Name);
                    Range range = worksheet.UsedRange;
                    object[,] values = (object[,])range.Value2;
                    for (int i = 1; i <= range.Rows.Count; i++)
                    {
                        string rowString = "";
                        for (int j = 1; j <= range.Columns.Count; j++)
                        {
                            if (values[i, j] != null)
                                rowString += values[i, j].ToString() + ",";
                            else
                                rowString += ",";
                        }
                        // 为每一列增加 来自哪个表
                        writer.WriteLine(worksheet.Name + "," + rowString.TrimEnd(','));
                    }
                    Marshal.ReleaseComObject(worksheet); // 释放资源 
                }

                writer.Close();
            }
            // 关闭Excel应用程序，释放资源 
            workbook.Close(false);
            excel.Quit();
        }

        public static void ReadExcel()
        {
            // 文件名
            string fileName = "xxx.xls";
            string connectionStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fileName + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IME=1\"";
            OleDbConnection oleDbConnection = new OleDbConnection(connectionStr);

            oleDbConnection.Open();

            string sql = "select * from [Sheet1$]";

            // 数据处理
            OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter(sql, connectionStr);

            // 数据集合
            DataSet excelData = new DataSet();

            // 添加到DataSet中
            oleDbDataAdapter.Fill(excelData);

            oleDbConnection.Close();

            // 表格集合
            DataTableCollection tableCollection = excelData.Tables;
            DataTable table = tableCollection[0];
            DataRowCollection rowCollection = table.Rows;
            // 遍历行
            foreach (DataRow row in rowCollection)
            {
                for (int i = 0; i < 8; i++)
                {
                    Console.Write(row[i] + " ");
                }
                Console.WriteLine();
            }
        }

        public static void Main(string[] args)
        {
            // Gelly.Merge("DTC List_GEELY_G636.xlsx");

            Gelly.Removed(@"D:\ExcelData\DTCSUM\1\2.xlsx");
        }


        /**
		 * 多个工作表合并为一个
		 */
        static void SumRos()
        {
            Excel.Application excelApp = new Excel.Application();
            Excel.Workbook workbook = excelApp.Workbooks.Open(@"D:\data\DTC List_GEELY_FS11 A2.xlsx");

            Excel.Worksheet destWorksheet = (Excel.Worksheet)workbook.Sheets[1];
            for (int i = 2; i <= workbook.Sheets.Count; i++)
            {
                Excel.Worksheet srcWorksheet = (Excel.Worksheet)workbook.Sheets[i];
                int lastRow = destWorksheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
                int copyStartRow = lastRow + 1;
                int copyEndRow = copyStartRow + srcWorksheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row - 1;
                srcWorksheet.Range["A1", srcWorksheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell)].Copy(destWorksheet.Range["A" + copyStartRow.ToString(), "Z" + copyEndRow.ToString()]);
            }

            workbook.SaveAs(@"D:\data\sum\DTC List_GEELY_FS11 A2.xlsx");
            workbook.Close();
            excelApp.Quit();

        }

       
        //static void Main(string[] args)
        //{
        //	// 打开Excel文件
        //	Application excel = new Application();
        //	Workbook workbook = excel.Workbooks.Open(@"C:\path\to\excel_file.xlsx");

        //	// 遍历每个工作表并执行操作（在这里我们只打印名称）
        //	foreach (Worksheet worksheet in workbook.Worksheets)
        //	{
        //		Console.WriteLine(worksheet.Name);
        //		// 在此处执行您想要对工资表进行的任何其他操作

        //		Marshal.ReleaseComObject(worksheet); // 释放资源 
        //	}
        //	// 关闭Excel应用程序，释放资源 
        //	workbook.Close(false);
        //	excel.Quit();
        //}
    }
}
